import mysql from 'mysql2/promise';
import { dbConfig } from './configuration.js';
import logger from './logger.js';
let pool = null;

async function getPool() {
    if (pool) {
        return pool;
    }
    try {
        pool = mysql.createPool(dbConfig);
        await pool.query('SELECT 1 + 1 ');
        logger.info('mysql 链接成功');
    } catch (error) {
        logger.error('createPool error: ', error.message, 'dbConfig: ', JSON.stringify(dbConfig));
        return error;
    }

    return pool;
}

async function query(sql, params) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [rows] = await pool.query(sql, params);
        return rows;
    } catch (error) {
        logger.error(error);
        return error;
    }
}

async function findOne(table, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [rows] = await pool.query(
            `SELECT * FROM ${table} WHERE ? LIMIT 1`,
            conditions
        );
        return rows[0];
    } catch (error) {
        logger.error(
            'findOne error: ',
            error.message,
            table,
            JSON.stringify(conditions)
        );
        return error;
    }
}

//根据对象生成where语句
function whereString(conditions, areas = []) {
    let where = ' WHERE ';
    let params = [];
    for (let key in conditions) {
        if (conditions[key] != undefined) {
            where += `${key}=? AND `;
            params.push(conditions[key]);
        }
    }


    areas.forEach((area) => {
        where += ` ${area.field} between ? AND ?`;
        params.push(area.start);
        params.push(area.end);
    });
    if (where == ' WHERE ') {
        where = '';
    }
    else {
        if (areas.length == 0) where = where.substring(0, where.length - 4);
    }





    return { where, params };
}




/**
 *
 * @param {*} table string
 * @param {*} conditions 条件 {}
 * @param {*} orderBy string
 * @param {*} direction strirng
 * @param {*} limit
 * @param {*} offset
 * @returns
 */
async function findAll(
    table,
    conditions,
    orderBy = null,
    direction = 'ASC',
    limit = null,
    offset = null,
    needCount = false,
    areas = [],
    flelds = '*',
    joinTable = '',
    joinCondition = '',
    inwhere='',
) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let count = 0;
    let { where, params } = whereString(conditions, areas);

    if(where!='' && inwhere!=''){
        where=` ${where}  and ${inwhere}`;
     }   
     else if (inwhere!='')
     {
         where=`where  ${inwhere}`;
     }

     let sql='';
    if (needCount) {
        if (joinTable != '') {
            sql=`SELECT COUNT(*) as count FROM ${table}  LEFT JOIN ${joinTable} ON ${joinCondition} ${where}`;
        }
        else{
            sql = `SELECT COUNT(*) as count FROM ${table}  ${where}`;
        }
        const [rows] = await pool.query(sql, params);
        count = rows[0].count;
        if (limit && count == 0) {
            return { count, row: [] };
        }
    }

    if (joinTable != '') {
        sql=`SELECT ${flelds} FROM ${table}  LEFT JOIN ${joinTable} ON ${joinCondition} ${where}`;
    }
    else{
        sql = `SELECT * FROM ${table}  ${where}`;
    }
    if (orderBy) {
        sql += ` ORDER BY ${orderBy} ${direction}`;
    }
    if (limit) {
        offset = limit * (offset - 1);
        sql += ` LIMIT ${offset},${limit}`;
    }

    try {
        logger.info(sql);
        const [rows] = await pool.query(sql, params);
        return needCount
            ? {
                count,
                rows,
            }
            : rows;
    } catch (error) {
        logger.error(
            'findAll error: ',
            error.message,
            table,
            JSON.stringify(conditions)
        );
        return null;
    }
}

async function update(table, data, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let where = '';
    //遍历conditions 生成where语句
    for (let key in conditions) {
        if (conditions[key] != undefined) {
            where += `${key}='${conditions[key]}' AND `;
        }
    }
    where = where.substring(0, where.length - 4);
    try {
        const [result] = await pool.query(`UPDATE ${table} SET ? WHERE ${where}`, [data]);
        return result;
    } catch (error) {
        logger.error(
            'update error: ',
            error.message,
            table,
            JSON.stringify(data),
            JSON.stringify(conditions)
        );
        return error;
    }
}
/**
 *
 * @param {*} table
 * @param {*} conditions
 * @returns
 */
async function deleteRow(table, conditions) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    let where = '';
    //遍历conditions 生成where语句
    for (let key in conditions) {
        if (conditions[key] != undefined) {
            where += `${key}='${conditions[key]}' AND `;
        }
    }
    where = where.substring(0, where.length - 4);
    try {
        const [result] = await pool.query(
            `DELETE FROM ${table} WHERE ${where}`);
        return result;
    } catch (error) {
        logger.error(
            'delete error: ',
            error.message,
            table,
            JSON.stringify(conditions)
        );
        return error;
    }
}

async function close() {
    if (pool) {
        await pool.end();
    }
}

//执行事务
async function transaction(sqls) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();
        for (let i in sqls) {
            await conn.query(sqls[i].sql, sqls[i].param);
        }
        await conn.commit();
        // logger.info(result);
        conn.release();
        return 1;
    } catch (error) {
        await conn.rollback();
        logger.error(
            'transaction error: ',
            error.message,
            JSON.stringify(sqls)
        );
        conn.release();
        return 0;
    }
}

async function excute(sql, params) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [rows] = await pool.query(sql, params);
        return rows;
    } catch (e) {
        return e;
    }
}

async function create(table, data) {
    const pool = await getPool();
    if (!pool) {
        return null;
    }
    try {
        const [result] = await pool.query(`INSERT INTO ${table} SET ?`, data);
        return result;
    } catch (error) {
        logger.error(
            'create error: ',
            error.message,
            table,
            JSON.stringify(data)
        );
        return error;
    }
}
//根据对象生成插入的sql语句
function getInsertSql(obj,tableName){
    let sql=`insert into ${tableName} (`;
    let values=' values(';
    for(let key in obj){
        sql+=key+',';
        values+=`'${obj[key]}',`;
    }
    sql=sql.substring(0,sql.length-1)+')';
    values=values.substring(0,values.length-1)+')';
    console.log("getInsertSql:",sql+values);
    return sql+values;
}
//根据对象生成更新的sql语句
function getUpdateSql(obj,tableName,where){
    let sql=`update ${tableName} set `;
    for(let key in obj){
        sql+=`${key}='${obj[key]}',`;
    }
    sql=sql.substring(0,sql.length-1);
    sql+=` where ${where}`;
    console.log("getUpdateSql:",sql);
    return sql;
}

export default {
    getPool,
    query,
    findOne,
    update,
    deleteRow,
    findAll,
    close,
    transaction,
    excute,
    create,
    getInsertSql,
    getUpdateSql
};

/**
 * where 条件
 * @param {*} conditions
 * @returns ['', []]
 */
// function whereString(conditions = {}) {
//     if (Object.keys(conditions).length == 0) {
//         return ['', []];
//     }
//     let where = ' where ';
//     let str = '';
//     let arr = [];
//     for (let i in conditions) {
//         str = '';
//         if (
//             /\>\=|\>|\<\=|\<|\$in|\$notin|\$like|!=/.test(String(conditions[i]))
//         ) {
//             str = ` ${i} ${conditions[i]} and`;
//             str = str.replace('$', '');
//         } else {
//             str = ` ${i} = ? and`;
//             arr.push(conditions[i]);
//         }
//         where += str;
//     }
//     where = where.replace(/^(.*)and$/, '$1');
//     return [where, arr];
// }
